--    Program: 		CAPPOIMP_PURCHASE_ORDER_DISTRIBUTION.sql
--    Description:  Import PO Distribution All information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         23-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp    		varchar2(2000);
   
   l_po_distribution_id		number;
                    		                 		
   cursor cur_distributions_all is
      select * from CRST_PO_DISTRIBUTIONS_ALL for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_distributions_all loop   	
     l_err_msg_tmp := '';
     
     SELECT PO_DISTRIBUTIONS_S.NEXTVAL 
     INTO l_po_distribution_id
     FROM DUAL;
     
     update CRST_PO_DISTRIBUTIONS_ALL
     set    po_distribution_id_n = l_po_distribution_id
     where  po_distribution_id = cur.PO_DISTRIBUTION_ID;
     
     begin     
       -- import data
       insert into PO_DISTRIBUTIONS_ALL 
       (
            PO_DISTRIBUTION_ID           ,
            LAST_UPDATE_DATE             ,
            LAST_UPDATED_BY              ,
            PO_HEADER_ID                 ,
            PO_LINE_ID                   ,
            LINE_LOCATION_ID             ,
            SET_OF_BOOKS_ID              ,
            CODE_COMBINATION_ID          ,
            QUANTITY_ORDERED             ,
            LAST_UPDATE_LOGIN            ,
            CREATION_DATE                ,
            CREATED_BY                   ,
            PO_RELEASE_ID                ,
            QUANTITY_DELIVERED           ,
            QUANTITY_BILLED              ,
            QUANTITY_CANCELLED           ,
            REQ_HEADER_REFERENCE_NUM     ,
            REQ_LINE_REFERENCE_NUM       ,
            REQ_DISTRIBUTION_ID          ,
            DELIVER_TO_LOCATION_ID       ,
            DELIVER_TO_PERSON_ID         ,
            RATE_DATE                    ,
            RATE                         ,
            AMOUNT_BILLED                ,
            ACCRUED_FLAG                 ,
            ENCUMBERED_FLAG              ,
            ENCUMBERED_AMOUNT            ,
            UNENCUMBERED_QUANTITY        ,
            UNENCUMBERED_AMOUNT          ,
            FAILED_FUNDS_LOOKUP_CODE     ,
            GL_ENCUMBERED_DATE           ,
            GL_ENCUMBERED_PERIOD_NAME    ,
            GL_CANCELLED_DATE            ,
            DESTINATION_TYPE_CODE        ,
            DESTINATION_ORGANIZATION_ID  ,
            DESTINATION_SUBINVENTORY     ,
            ATTRIBUTE_CATEGORY           ,
            ATTRIBUTE1                   ,
            ATTRIBUTE2                   ,
            ATTRIBUTE3                   ,
            ATTRIBUTE4                   ,
            ATTRIBUTE5                   ,
            ATTRIBUTE6                   ,
            ATTRIBUTE7                   ,
            ATTRIBUTE8                   ,
            ATTRIBUTE9                   ,
            ATTRIBUTE10                  ,
            ATTRIBUTE11                  ,
            ATTRIBUTE12                  ,
            ATTRIBUTE13                  ,
            ATTRIBUTE14                  ,
            ATTRIBUTE15                  ,
            WIP_ENTITY_ID                ,
            WIP_OPERATION_SEQ_NUM        ,
            WIP_RESOURCE_SEQ_NUM         ,
            WIP_REPETITIVE_SCHEDULE_ID   ,
            WIP_LINE_ID                  ,
            BOM_RESOURCE_ID              ,
            BUDGET_ACCOUNT_ID            ,
            ACCRUAL_ACCOUNT_ID           ,
            VARIANCE_ACCOUNT_ID          ,
            PREVENT_ENCUMBRANCE_FLAG     ,
            USSGL_TRANSACTION_CODE       ,
            GOVERNMENT_CONTEXT           ,
            DESTINATION_CONTEXT          ,
            DISTRIBUTION_NUM             ,
            SOURCE_DISTRIBUTION_ID       ,
            REQUEST_ID                   ,
            PROGRAM_APPLICATION_ID       ,
            PROGRAM_ID                   ,
            PROGRAM_UPDATE_DATE          ,
            PROJECT_ID                   ,
            TASK_ID                      ,
            EXPENDITURE_TYPE             ,
            PROJECT_ACCOUNTING_CONTEXT   ,
            EXPENDITURE_ORGANIZATION_ID  ,
            GL_CLOSED_DATE               ,
            ACCRUE_ON_RECEIPT_FLAG       ,
            EXPENDITURE_ITEM_DATE        ,
            ORG_ID                       ,
            KANBAN_CARD_ID               ,
            AWARD_ID                     ,
            MRC_RATE_DATE                ,
            MRC_RATE                     ,
            MRC_ENCUMBERED_AMOUNT        ,
            MRC_UNENCUMBERED_AMOUNT      ,
            END_ITEM_UNIT_NUMBER         ,
            TAX_RECOVERY_OVERRIDE_FLAG   ,
            RECOVERABLE_TAX              ,
            NONRECOVERABLE_TAX           ,
            RECOVERY_RATE                ,
            OKE_CONTRACT_LINE_ID         ,
            OKE_CONTRACT_DELIVERABLE_ID  
       )
       values
       (
            l_po_distribution_id             ,
            cur.LAST_UPDATE_DATE             ,
            CUR.LAST_UPDATED_BY,
            CAPPOIMP_MAPPING.GetPOHeaderIDMapping(cur.PO_HEADER_ID),
            CAPPOIMP_MAPPING.GetPOLineIDMapping(cur.PO_LINE_ID),
            CAPPOIMP_MAPPING.GetPOLineLocationIDMapping(cur.LINE_LOCATION_ID),
            CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.CODE_COMBINATION_ID),
            cur.QUANTITY_ORDERED             ,
            cur.LAST_UPDATE_LOGIN            ,
            cur.CREATION_DATE                ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.PO_RELEASE_ID                ,
            cur.QUANTITY_DELIVERED           ,
            cur.QUANTITY_BILLED              ,
            cur.QUANTITY_CANCELLED           ,
            cur.REQ_HEADER_REFERENCE_NUM     ,
            cur.REQ_LINE_REFERENCE_NUM       ,
            cur.REQ_DISTRIBUTION_ID          ,
            CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.DELIVER_TO_LOCATION_ID), 
            CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.DELIVER_TO_PERSON_ID),
            cur.RATE_DATE                    ,
            cur.RATE                         ,
            cur.AMOUNT_BILLED                ,
            cur.ACCRUED_FLAG                 ,
            cur.ENCUMBERED_FLAG              ,
            cur.ENCUMBERED_AMOUNT            ,
            cur.UNENCUMBERED_QUANTITY        ,
            cur.UNENCUMBERED_AMOUNT          ,
            cur.FAILED_FUNDS_LOOKUP_CODE     ,
            cur.GL_ENCUMBERED_DATE           ,
            cur.GL_ENCUMBERED_PERIOD_NAME    ,
            cur.GL_CANCELLED_DATE            ,
            cur.DESTINATION_TYPE_CODE        ,
            CAPBRIMP_MAPPING.GetOrganizationMapping(cur.DESTINATION_ORGANIZATION_ID), 
            cur.DESTINATION_SUBINVENTORY     ,
            cur.ATTRIBUTE_CATEGORY           ,
            cur.ATTRIBUTE1                   ,
            cur.ATTRIBUTE2                   ,
            cur.ATTRIBUTE3                   ,
            cur.ATTRIBUTE4                   ,
            cur.ATTRIBUTE5                   ,
            cur.ATTRIBUTE6                   ,
            cur.ATTRIBUTE7                   ,
            cur.ATTRIBUTE8                   ,
            cur.ATTRIBUTE9                   ,
            cur.ATTRIBUTE10                  ,
            cur.ATTRIBUTE11                  ,
            cur.ATTRIBUTE12                  ,
            cur.ATTRIBUTE13                  ,
            cur.ATTRIBUTE14                  ,
            cur.ATTRIBUTE15                  ,
            cur.WIP_ENTITY_ID                ,
            cur.WIP_OPERATION_SEQ_NUM        ,
            cur.WIP_RESOURCE_SEQ_NUM         ,
            cur.WIP_REPETITIVE_SCHEDULE_ID   ,
            cur.WIP_LINE_ID                  ,
            cur.BOM_RESOURCE_ID              ,
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.BUDGET_ACCOUNT_ID),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.ACCRUAL_ACCOUNT_ID),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.VARIANCE_ACCOUNT_ID),
            cur.PREVENT_ENCUMBRANCE_FLAG     ,
            cur.USSGL_TRANSACTION_CODE       ,
            cur.GOVERNMENT_CONTEXT           ,
            cur.DESTINATION_CONTEXT          ,
            cur.DISTRIBUTION_NUM             ,
            cur.SOURCE_DISTRIBUTION_ID       ,
            cur.REQUEST_ID                   ,
            cur.PROGRAM_APPLICATION_ID       ,
            cur.PROGRAM_ID                   ,
            cur.PROGRAM_UPDATE_DATE          ,
            cur.PROJECT_ID                   ,
            cur.TASK_ID                      ,
            cur.EXPENDITURE_TYPE             ,
            cur.PROJECT_ACCOUNTING_CONTEXT   ,
            cur.EXPENDITURE_ORGANIZATION_ID  ,
            cur.GL_CLOSED_DATE               ,
            cur.ACCRUE_ON_RECEIPT_FLAG       ,
            cur.EXPENDITURE_ITEM_DATE        ,
            CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
            cur.KANBAN_CARD_ID               ,
            cur.AWARD_ID                     ,
            cur.MRC_RATE_DATE                ,
            cur.MRC_RATE                     ,
            cur.MRC_ENCUMBERED_AMOUNT        ,
            cur.MRC_UNENCUMBERED_AMOUNT      ,
            cur.END_ITEM_UNIT_NUMBER         ,
            cur.TAX_RECOVERY_OVERRIDE_FLAG   ,
            cur.RECOVERABLE_TAX              ,
            cur.NONRECOVERABLE_TAX           ,
            cur.RECOVERY_RATE                ,
            cur.OKE_CONTRACT_LINE_ID         ,
            cur.OKE_CONTRACT_DELIVERABLE_ID   
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;	 
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_PURCHASE_ORDER_DISTRIBUTION', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
